/*
 * PortfolioDB.java
 *
 * Created on April 9, 2005, 6:15 PM
 * 13, Feb 2006 Bebining of overhaul works for the Netbeans 5 release. 
 * 15, Sep 2006 Converted into constructorless class.
 * 07, May 2007 The class changed mane from PortfolioDB to MiscellaneaDB
 * 06, Sep 2007 moved from package portfolio to misellanea
 * 25, Oct 2009 due to the improvisation of this code, from now on, the methods of this class will be relocated on the classes that rightfully pertrain to,
 *              and this class eventually will be deprecated.
 */

package misellanea;

import java.sql.*;
import java.util.Vector;
import java.util.Calendar;
import java.io.*;
import javax.swing.table.DefaultTableModel;
import portfolio.dbHost.dataBases.DBase;

/**
 *
 * @author  ACA
 */
public class MiscellaneaDB {
    /**
     *
     */
    public static double [] insertStock2Pflio (Connection  connectParam,
                                               String      sNameParam, 
                                               Vector      vTimeLineParam, 
                                               double [][] inputDataParam, 
                                               int         iTermParam) throws Exception
    {
        Statement statement   = null;
        double [] dReturnVect = null;
        double    dReturn       = 0.0;
        double [] dReturnRisk  = null;
        
        if (vTimeLineParam.size() == 0) {
            throw new Exception ("PortfolioDB::insertStock2Pflio: empty timeline");
        }
        
        System.out.println ("New stock inserted into portfolio");
        try {
            statement = connectParam.createStatement();
/*>*/       createTradeDateColumn  (connectParam, "portfolio", "returnSeries");
/*>*/       createStockColumn (connectParam, "returnSeries", sNameParam);
/*>*/       dReturnVect = insertReturnSeries (connectParam, sNameParam, inputDataParam, vTimeLineParam, iTermParam);
            dReturn     = Miscellanea.getReturn  (dReturnVect, iTermParam);

            double dRisk      = Miscellanea.getRisk (dReturnVect, dReturn);
//            System.out.println (sNameParam+ " risk: " + dRisk + " return " + dReturn + "\n");
            
/*>*/       insertStock (connectParam, sNameParam, dReturn, dRisk);
            
            dReturnRisk = new double [2];
            dReturnRisk [0] = dReturn;
            dReturnRisk [1] = dRisk;
        } catch (SQLException e) {
            //JOptionPane.showMessageDialog (null, e);
            throw new Exception (e);
        } catch (Exception e) {
            //JOptionPane.showMessageDialog (null, e);
            throw new Exception (e);
        }
        
        return dReturnRisk;
    }
    /**
     *
     */
    public static void createTradeDateColumn (Connection connectParam, String sDBName, String sTable ) throws Exception
    {
        Statement statement = null;
        try {
            statement = connectParam.createStatement ();
        } catch (SQLException sqle) {
            throw new Exception ("portfolioDB::createTradeDateColumn: "+sqle);
        }          
        ResultSet rs;
        int iColumn = 0;
        
 	try {
            statement.execute ("USE " + sDBName); //portfolio;");  
            rs = statement.executeQuery ("SELECT * FROM "+ sTable +";");
            iColumn = rs.findColumn ("tradeDate");
        } catch ( SQLException sqlex ) {
            try {
                statement.execute ("CREATE TABLE " + sTable + " (tradeDate DATE, INDEX (tradeDate));"); 
            } catch (SQLException sqle) {
                throw new Exception ("portfolioDB::createTradeDateColumn: "+sqle.getMessage());
            }
	}
        return;
    }
    /**
     *
     */
    public static void createInsertTradeDateColumn (Connection connectParam, String sDBName, String sTable, Vector vTradeDate) throws Exception
    {
        Statement statement = null;
        try {
            statement = connectParam.createStatement ();
        } catch (SQLException sqle) {
            throw new Exception ("portfolioDB::createTradeDateColumn: " + sqle);
        }          
        ResultSet rs = null;
        int iColumn = 0;
        
 	try {
            statement.execute ("USE " + sDBName); //portfolio;");  
            rs = statement.executeQuery ("SELECT * FROM "+ sTable +";");
            iColumn = rs.findColumn ("tradeDate");
            rs.last ();
            if (rs.getRow () != 0) {
                return;
            }
            rs.first();            
        } catch ( SQLException sqlex ) {
            try {
                statement.execute ("CREATE TABLE " + sTable + " (tradeDate DATE, INDEX (tradeDate));"); 
            } catch (SQLException sqle) {
                //System.err.println ("MainScr::createReadeDate: "+sqle);
                //JOptionPane.showMessageDialog (null, "MainScr::createReadeDate: " + sqle);
                throw new Exception ("portfolioDB::createTradeDateColumn: "+sqle);
            }
	}

        try {
            int iSize = vTradeDate.size();
            if (iSize <= 0) {
                throw new Exception ("portfolioDB::createTradeDateColumn: No TradeDate time vector.");
            }
            for (int i = 0; i < iSize; i++) {
                statement.execute ("INSERT INTO  " + sTable +" (tradeDate) VALUES ('" + 
                                    vTradeDate.get(i).toString() + "');");
            }
        } catch (SQLException sqlex) {
            throw new Exception ("portfolioDB::createTradeDateColumn: "+sqlex);
        }
        return;
    }
    /**
     *
     */
    public static void createStockColumn (Connection connectParam, String sTable, String sColumnParam) throws Exception
    {
        Statement statement = null;
        try {
            statement = connectParam.createStatement ();
        } catch (SQLException sqle) {
            throw new Exception ("portfolioDB::createStockColumn: "+sqle);
        }          
        ResultSet rs;
        
        try {
            //statement.execute ("USE portfolio;");
            rs = statement.executeQuery ("SELECT * FROM " + sTable + ";"); //returnSeries;");
            rs.findColumn (sColumnParam);
            statement.execute ("ALTER TABLE " + sTable + " DROP COLUMN " + sColumnParam + ";");
            statement.execute ("ALTER TABLE " + sTable + " ADD COLUMN " + sColumnParam + " FLOAT;");
        } catch ( SQLException sqlex ) {
            try {
                statement.execute ("ALTER TABLE " + sTable + " ADD COLUMN " + sColumnParam + " FLOAT;");
            } catch (SQLException sqle) {
                //sqle.printStackTrace();
                throw new Exception ("portfolioDB::createStockColumn: "+sqle);
            }
        }        
        return;
    }    
    /**
     *
     */
    public static double [] insertReturnSeries(Connection   connectParam,
                                               String      sNameParam, 
                                               double [][] inputDataParam, 
                                               Vector      vTimeLineParam,
                                               int         iTermParam) throws Exception
    {
        Statement statement = null;
        try {
            statement = connectParam.createStatement ();
        } catch (SQLException sqle) {
            throw new Exception ("portfolioDB::insertReturnSeries: "+sqle);
        }        
        ResultSet rs;
        int       iColumn  = 0;
        int       iDateRow = 0;
        int       iSize    = vTimeLineParam.size();
        double    dReturn  = 0.0;
        int       i        = 0;

        if (iSize < iTermParam)
            throw new Exception ("portfolioDB::insertReturnSeries: Return vector can't be built; iSize < iTerm.");
        
        double [] dReturnVect = new double [iSize -1];
        
        try {
            //statement.execute ("USE portfolio;");
            rs = statement.executeQuery ("SELECT * FROM returnSeries;");
            iColumn = rs.findColumn ("tradeDate");
            rs.last ();
            iDateRow = rs.getRow();               
            iColumn = rs.findColumn (sNameParam);
            Date tradeDate = null;
            
            if (iDateRow > 0) {
                rs.first();
                for (i = iSize - iTermParam; i < iSize; i++) {
                    dReturn = (inputDataParam [i-1][1] != 0)? // dReturn +
                                      ((inputDataParam [i][1] - inputDataParam[i-1][1])/inputDataParam [i-1][1]):0;//dReturn;
                    statement.execute ("UPDATE returnSeries SET " +
                                       sNameParam + "=" + 
                                       dReturn + 
                                       " " + "WHERE tradeDate='" + vTimeLineParam.get(i).toString () +"';");
                    dReturnVect [i -1] = dReturn;
                    
                }
            }    
            else {
                for (i = iSize - iTermParam; i < iSize; i++) {
                    dReturn = (inputDataParam [i-1][1] != 0)? //dReturn +
                                      ((inputDataParam [i][1] - inputDataParam[i-1][1])/inputDataParam [i-1][1]):0;//dReturn;
                    statement.execute ("INSERT INTO returnSeries (tradeDate, " +
                                        sNameParam+") VALUES ('" + 
                                        vTimeLineParam.get(i).toString() + "', "+
                                        dReturn +
                                        ");");
                    dReturnVect [i -1] = dReturn;
                }                
            }
        } catch (SQLException sqlex) {    
            //sqlex.printStackTrace();
            throw new Exception ("portfolioDB::insertReturnSeries: " + sqlex);
        }
        return dReturnVect;
    }    
    /**
     * Note: on MYSQL v14.12 the word "return" is reserved and can't be used to create DB objects; therefore the table stocks cannot contain a column
     * called return.
     */
    public static void insertStock (Connection connectParam, String sNameParam, double dReturnParam, double dRiskParam) throws Exception
    {
        ResultSet rs;
        Statement statement = null;
        try {
            statement = connectParam.createStatement (); //statementParam;
        } catch (SQLException sqle) {
            //sqle.printStackTrace();
            throw new Exception ("portfolioDB::insertStock: "+sqle);
        }
        
        try {
            rs = statement.executeQuery ("SELECT * FROM stocks;");
        } catch ( SQLException sqlex ) {
            try {
                statement.execute ("CREATE TABLE stocks (stockName CHAR (20) UNIQUE, return_ FLOAT, risk FLOAT, INDEX (stockName));");
            } catch (SQLException sqle) {
                //sqle.printStackTrace();
                throw new Exception ("portfolioDB::insertStock: "+sqle);
            }
        }
        
        try {
            statement.execute ("REPLACE stocks (stockName, return_, risk) VALUES ('" + 
                               sNameParam + "', " +
                               dReturnParam + ", " +
                               dRiskParam +");");
        } catch (Exception e) {
                //e.printStackTrace();
                throw new Exception ("portfolioDB::insertStock: " + e);
        }       
        return; // true;
    }    
    /**
     *
     */
    public static Vector getStocksReturn (Connection connectParam, Vector vStockNameColumn) throws Exception
    {
        ResultSet rs;
        //double dStockReturn = 0.0; 
        Statement statement = null;
        try {
            statement = connectParam.createStatement (); //statementParam;
        } catch (SQLException sqle) {
            sqle.printStackTrace ();
            throw new Exception ("portfolioDB::getStockReturn: "+sqle);
        }
        
        Vector vStocksReturn = new Vector ();
        if (vStockNameColumn.isEmpty() == false) {
            
            int iVectSize = vStockNameColumn.size();

            try {
                for (int i = 0; i < iVectSize; i++) {
                    String sStockName = (String) vStockNameColumn.get (i);
                    rs = statement.executeQuery ("SELECT return FROM stocks WHERE stockName = '" + sStockName + "';");
                    rs.first ();
                    //System.out.println ("row: "+ rs.getRow());
                    Double DStockReturn = new Double (rs.getDouble(1));
                    vStocksReturn.addElement (DStockReturn);
                }
            } catch ( SQLException sqlex ) {

                throw new Exception ("portfolioDB::insertStock: "+sqlex);
            }
        }
        else {
            vStocksReturn = null;
        }
  
        return vStocksReturn;
    }    
    /**
     *
     */
    public static Vector describeReturnSeriesTable (Connection connectParam, String sTableName ) throws Exception
    {
        Vector outVector = new Vector ();
        try {
            Statement statement = connectParam.createStatement ();
            statement.execute ("USE portfolio;");
            ResultSet resultSet = statement.executeQuery ("DESCRIBE " + sTableName + ";"); //"returnSeries"+";");
            if (!resultSet.next ()) {
                throw new Exception ("portfolioDB::describeReturnSeriesTable: resultSet = null");
            }
            ResultSetMetaData rsmd = resultSet.getMetaData ();
//          System.out.println ("rsmd: " + rsmd.getColumnCount ());
//          System.out.println ("rs string " + resultSet.getString (1));
            do {
                if (!resultSet.getString (1).equals ("tradeDate")) {  //0j0 to lower
                    //System.out.println (resultSet.getString (1)+"+++++++++++++++++++++++");
                    outVector.addElement (resultSet.getString (1));
                }
            } while (resultSet.next ());
        }
        catch (SQLException sqlex) {
            //sqlex.printStackTrace ();
            throw new Exception ("portfolioDB::describeReturnSeriesTable: " + sqlex);
        }

        return (outVector);
    } /* End Method MainScr::describePortfolioTable */      
    /**
     *
     */
    public static double [][] selectReturnSeriesTable (Connection connectParam, String sTableName, Vector vColumnNamesParam) throws Exception
    {
        ResultSet resultSet;
        Statement statement      = null;
        Vector vColumnNames      = vColumnNamesParam;
        String sColumnNames      = null;
        double [][] dTimeSeries  = null;
        
        try {
            statement = connectParam.createStatement ();
            statement.execute ("USE portfolio;");
            
            sColumnNames = vColumnNamesParam.toString();
            sColumnNames = sColumnNames.substring (1, sColumnNames.length () -1);
            
            //System.out.println ("Vector:\n" + sColumnNames);
            
            resultSet = statement.executeQuery ("SELECT " + sColumnNames +" FROM " + sTableName + " ORDER BY tradeDate");
                                                                                    //returnSeries ORDER BY tradeDate;");
            
            ResultSetMetaData rsmd = resultSet.getMetaData ();
            int iColumn = rsmd.getColumnCount ();
            resultSet.last();
            int iRow = resultSet.getRow();
            resultSet.first();
            
            dTimeSeries = new double [iRow][iColumn];
            
            for (int j = 0;j < iRow; j++) {
                //outVector.addElement (resultSet.getString (1));
                for (int i = 1; i <= iColumn; i++) {
                    dTimeSeries [j][i -1] = resultSet.getDouble (i);
                    //System.out.print (dTimeSeries [j][i -1] +"\t");
                }
                //System.out.println ();
                resultSet.next ();
            }
            //System.out.println ("Column: " + iColumn);
            //System.out.println ("row: " + iRow );
        } catch (SQLException sqle) {
            throw new Exception ("portfolioDB::selectReturnSeriesTable: "+sqle);
        }        
        return dTimeSeries;
    }
    /**
     *
     */
// 0j0: this metohod uses static nomenclature, which by V0.5.01 is obsolete. But there is functionalty yet to be implemented.
// KEEP this code for a while.    
    public static Vector selectTradedateFromReturnSeriesTable (Connection connectParam, String sTableName ) throws Exception
    {
        Vector outVector = new Vector ();
        try {
            Statement statement = connectParam.createStatement ();
            statement.execute ("USE portfolio;");
            ResultSet resultSet = statement.executeQuery ("SELECT tradeDate FROM " + sTableName + " ORDER BY tradeDate"+";");
            if (!resultSet.next ()) {
                throw new Exception ("portfolioDB::selectTradedateFromReturnSeriesTable: resultSet = null");
            }
            ResultSetMetaData rsmd = resultSet.getMetaData ();
            //System.out.println ("rsmd: " + rsmd.getColumnCount ());
            //System.out.println ("rs string " + resultSet.getString (1));
            do {
                    outVector.addElement (resultSet.getString (1));
            } while (resultSet.next ());
        }
        catch (SQLException sqlex) {
            throw new Exception ("portfolioDB::selectTradedateFromReturnSeriesTable: " + sqlex);
        }

        return (outVector);
    } /* End Method MainScr::describePortfolioTable */
    /*****************************************************************
    * Former dropDB
    ******************************************************************/
    static public void dropDBTables (Connection connectionParam, String sDataBase) throws Exception
    {
        Statement statement = null;
        //ResultSet resultSet = null;
        try {
            statement = connectionParam.createStatement();
            //statement.execute ("use test;");
            statement.execute ("DROP DATABASE "+ sDataBase +";");
        } catch (SQLException sqlex) {
            //System.err.println("<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<");
        }
        try {
            statement.execute ("CREATE DATABASE "+ sDataBase +";"); //portfolio;");
        } catch (SQLException sqlex) {
            throw new Exception (sqlex);
        }
        return;
    }
    /******************************************************************
     * Note: dropTable is ready for anty injection.
     ******************************************************************/
    static public void dropTable (Connection connectionParam, String sTableParam) throws Exception
    {
        if (connectionParam == null)
            throw new Exception ("No connection provided.");
        if (sTableParam == null)
            throw new Exception ("No table name provided.");
        
        try {
            PreparedStatement pstmt = connectionParam.prepareStatement("DROP TABLE " + sTableParam);
            pstmt.execute ();                        
        } catch ( SQLException sqlex ) {
            throw new Exception (sqlex);
        }        
        return;
    }       
    /**
     *
     */
    public static double insertReturnToWitness (Connection connectParam,
                                                String     sNameParam, 
                                                Vector     vInputData, 
                                                Vector     vTimeLineParam) throws Exception
    {
        Statement statement = null;
        try {
            statement = connectParam.createStatement (); //statementParam;
        } catch (SQLException sqle) {
            throw new Exception ("portfolioDB::insertReturnSeries: "+sqle);
        }        
        ResultSet rs;
        int       iColumn  = 0;
        int       iDateRow = 0;
        int       iSize    = vTimeLineParam.size();
        double    dReturn  = 0.0;
        int       i        = 0;

        if (iSize < 2)
            throw new Exception ("portfolioDB::insertReturnSeries: Return vector can't be built; iSize < iTerm.");
        
        //double [] dReturnVect = new double [iSize -1];
        double dSumReturn = 0;
        
        try {
            statement.execute ("USE portfolio;");
            rs = statement.executeQuery ("SELECT * FROM witness;");
            iColumn = rs.findColumn ("tradeDate");
            rs.last ();
            iDateRow = rs.getRow();
            if (iDateRow != iSize) {
                throw new Exception ("portfolioDB::insertReturnSeries: Inconsistent vTimeLineParam and tradeDate.");
            }
        } catch (SQLException sqlex) {
            throw new Exception ("portfolioDB::insertReturnSeries: No tradeDate column.");
        }
        /**
         *
         */
        createStockColumn (connectParam, "witness", sNameParam);
        
        try {
            Double [] DInputData = new Double [iSize];
            vInputData.copyInto(DInputData);
            
            statement.execute ("UPDATE witness SET " +
                               sNameParam + "= 0 " +
                               "WHERE tradeDate='" + vTimeLineParam.get(i).toString () +"';");
            for (i = 1; i < iSize; i++) {
                double dPrice0 = DInputData [i-1].doubleValue();
                double dPrice1 = DInputData   [i].doubleValue();
                dReturn = (dPrice0 != 0)? 
                                  ((dPrice1 - dPrice0)/dPrice0):0;
                //System.out.println (dReturn);                  
                statement.execute ("UPDATE witness SET " +
                                   sNameParam + "=" + 
                                   dReturn + 
                                   " " + "WHERE tradeDate='" + vTimeLineParam.get(i).toString () +"';");
//                    dReturnVect [i -1] = dReturn;
                dSumReturn += dReturn;   
            }    
        } catch (SQLException sqlex) {    
            //sqlex.printStackTrace();
            throw new Exception ("portfolioDB::insertReturnSeries: " + sqlex);
        }
        return dSumReturn; //dReturnVect;
    }    
    /**
     *
     */
    public static double [] getWeightedReturn (double [][] dReturnSeries, Vector vWeights) throws Exception
    {
        //Statement statement      = null;
        //String sColumnNames      = null;
        
        int iColumn = dReturnSeries[0].length;
        int iRow = dReturnSeries.length;
        
        int iWColumn = vWeights.size();
        if (iWColumn != iColumn) {
            throw new Exception ("portfolioDB::getWeightedReturn: inconsistent iWColumn and iColumn.");
        }
        double [] dWeights = new double [iColumn];
        double [] dReturns = new double [iRow];
        
        for (int i = 0; i < iColumn; i++) {
            Double DTemp = new Double (vWeights.get(i).toString());
            dWeights [i] = DTemp.doubleValue();
//            System.out.print (dWeights [i] + " ");
        }
//        System.out.println ();

        for (int j = 0;j < iRow; j++) {
            double dReturn = 0;
            for (int i = 0; i < iColumn; i++) {
                dReturn += dReturnSeries [j][i] * dWeights [i];
                //System.out.print (dReturnSeries [j][i] +"\t");
            }
            dReturns [j] = dReturn;
            //System.out.println (dReturn);
        }
    
        return dReturns; // dTimeSeries;
    } /* End Method getWeightedReturns */
    /*****************************************************************************
    * Warning: this method parses SQL files generated by MYSQL dump utility;     *
    * due to the simplicity of the parsing algorithm, there are no warranties    *
    * of successful parsing with sql files genereted differently.                *
    * Note: another alterative to this method it to use                          *
    * LOAD DATA INFILE                                                           *
    ******************************************************************************/
    public static void executeSQLBatchFile (Connection connectionParam, String sFileName) throws Exception
    {
        if (connectionParam == null)
            throw new Exception ("null connection.");
        if (sFileName == null)
            throw new Exception ("null Filename.");
        
        try {
            FileReader     fRead  = new FileReader (sFileName); 
            BufferedReader buffIn = new BufferedReader (fRead);
            Statement statement   = connectionParam.createStatement();

            String sLine = null;
            String sCommand = null;
            
           while ( (sLine = buffIn.readLine()) != null ) {  
              if (sLine.startsWith("--"))
                  continue;
              int iCommentInit = sLine.indexOf("/*");
              int iCommentEnd   = 0;
              if (iCommentInit != -1) {
                  String sTmpLine = sLine;
                  while ((iCommentEnd = sTmpLine.indexOf("*/")) == -1) {
                      if ((sTmpLine = buffIn.readLine()) == null) 
                          throw new Exception ("portfolioDB::executeSQLBatchFile: " + "unexpected sql eof;");
                  }
                  sLine = sLine.substring(0, iCommentInit) + sTmpLine.substring(iCommentEnd +2, sTmpLine.length());
              }
              if (sLine.length() < 3)
                  continue;
              int iColonTmp = sLine.indexOf (";");
              if (iColonTmp > -1 && iColonTmp < 3)
                  continue;
              int iColon = sLine.indexOf (";");
              if (iColon == -1) {
                  sCommand =(sCommand == null)? sLine: sCommand + sLine;
                  continue;
              }
              sCommand =(sCommand == null)? sLine: sCommand + sLine;
              //System.out.println (sCommand);
              statement.execute (sCommand); //addBatch (sCommand);
              //Thread.sleep(100)
              sCommand = null;
           }
           //statement.executeBatch (); 
        } catch (Exception e) {
            throw new Exception ("portfolioDB::executeSQLBatchFile: " + e);
        }
        return;        
    } /* end method executeSQLBatchFile */
    /**
     *
     */
    public static void saveDBtoFile (Connection     connectParam,
                                     DBase       dBaseParam,
                                     BufferedWriter outFileWriter,
                                     String         sDatabase) throws Exception
    {
        if (connectParam == null)
            throw new Exception ("null connection.");
        if (dBaseParam == null)
            throw new Exception ("null dataBase.");
        if (outFileWriter == null)
            throw new Exception ("null outFileWriter.");
        if (sDatabase == null)
            throw new Exception ("null DataBase.");

        Vector vDBTables         = null;

        DBase    dBase = dBaseParam;
        
        outFileWriter.write ("CREATE DATABASE " + sDatabase + ";\n");
        outFileWriter.write ("USE " + sDatabase + ";\n");
        try {
            vDBTables = dBase.showTables();
            if (vDBTables.size() != 0) {
                for (int iIdx = 0; iIdx < vDBTables.size(); iIdx++) {
                    String sTable = vDBTables.get(iIdx).toString();
                    outFileWriter.write ("CREATE TABLE " + sTable + " ("); 

                    int iColumnCount = dBase.isTable(sTable).getColumnCount();
                    String sColumnNames = null;
                    String sIndexes     = null;
                    String sPrimaryKey  = null;
                    Vector vMultipleKey = new Vector ();
                    for (int iKdx = 0; iKdx < iColumnCount; iKdx++) {
                        String sField = dBase.isTable (sTable).getColumnDescription (iKdx).getField () + " ";
                        String sType  = dBase.isTable (sTable).getColumnDescription (iKdx).getType  () + " ";
                        String sNull  = dBase.isTable (sTable).getColumnDescription (iKdx).getNull  ();
                        String sTmp   = sField + sType + sNull + ",";
                        sColumnNames  = (sColumnNames == null)? sTmp: sColumnNames + sTmp;
                        String sKey   = dBase.isTable(sTable).getColumnDescription (iKdx).getKey ();
                        sColumnNames  = (sKey.equals("PRI"))? sColumnNames + " PRIMARY KEY (" + sField + "), ":
                                         sColumnNames;
                        if (sKey.equals("MUL")) {
                            vMultipleKey.addElement(sField);
                        }                                          
                    }
                    sColumnNames = sColumnNames.substring(0, sColumnNames.length() -1);
                    if (vMultipleKey.size() != 0) {
                        String sMultipleKey = vMultipleKey.toString();
                        sMultipleKey = sMultipleKey.substring (1, sMultipleKey.length() -1);
                        sColumnNames = sColumnNames + ", INDEX (" + sMultipleKey + ")";
                    }
                    outFileWriter.write (sColumnNames);
                    outFileWriter.write(");\n");
                    outFileWriter.write ("INSERT INTO " + sTable + " VALUES "); 
                    
                    Vector vTable [] = dBase.isTable(sTable).selectAllfromTable ();
                    int iTableLen = vTable[0].size();
                    int iTableWidth = vTable.length;
                    //System.out.println ("Len: " + iTableLen + " Size: " + iTableWidth);
                    for (int i = 0; i < iTableLen; i++) {
                        String sLine = null;
                        for (int j = 0; j < iTableWidth; j++) {
                            String sField = vTable [j].get(i).toString() + ",";
                            sLine =(sLine == null)? sField : sLine + sField;
                        }
                        sLine = sLine.substring (0, sLine.length() -1);
                        sLine = (i != iTableLen -1)?"(" + sLine + "),\n":
                                                      "(" + sLine + ");\n";
                        outFileWriter.write (sLine);
                    }
                    //outFileWriter.write (";\n");
                }
            } else {
                throw new Exception ("null Tables.");
            }
        } catch (Exception ex) {
            throw new Exception (ex.getMessage());
        }
    } /* end method saveDBtoFile */
    /**
     *
     */
//    public static boolean useDB (Connection connectionParam, String sDBNameParam)
//    {
//        try {
//            Statement statement = connectionParam.createStatement();
//            statement.execute ("USE " + sDBNameParam + ";");
//        }
//        catch ( Exception sqlex ) {
//            return false;
//        }
//        return true;
//    }
    /**
     *
     * @param rs 
     * @param tableModel 
     * @return 
     * @throws java.sql.SQLException 
     * @throws java.lang.Exception 
     */
    public static DefaultTableModel transferResultSet2TableModel (ResultSet rs, DefaultTableModel tableModel) throws SQLException, Exception
    {
        if (rs == null) {
            throw new Exception ("Null resultSet.");
        }
        boolean moreRecords = rs.next();  

        if ( ! moreRecords ) {
            throw new Exception ("ResultSet contained no records" );
        }

        Vector columnHeads = new Vector();

//        DefaultTableModel tableModel = new DefaultTableModel ();

        try {
            if (tableModel == null){
                throw new Exception ("data null.");
            }
            int iTableRows = tableModel.getRowCount ();
            if (iTableRows > 0) {
                int i = iTableRows;
                do {
                    tableModel.removeRow(--i);
                } while (tableModel.getRowCount () > 0);
            }
            ResultSetMetaData rsmd = rs.getMetaData ();

            tableModel.setColumnCount (rsmd.getColumnCount ());
            for ( int i = 1; i <= rsmd.getColumnCount (); ++i ) 
                columnHeads.addElement (rsmd.getColumnName (i));
            tableModel.setColumnIdentifiers (columnHeads);

            int i = 0;
            do {
                int iColumns = rsmd.getColumnCount();
                tableModel.insertRow (i++, MiscellaneaDB.getNextRowFromResultSet (rs, iColumns));

            } while (rs.next());
        }
        catch (SQLException sqlex) {
            throw new Exception (sqlex);
        }

        return (tableModel);
    } /* End method collectResultSet */
    /**
     *
     * @param rs 
     * @param iColumns 
     * @return 
     * @throws java.sql.SQLException 
     */
    public static Vector getNextRowFromResultSet (ResultSet rs, int iColumns) throws SQLException
    {
        Vector currentRow = new Vector ();

        for ( int i = 1; i <= iColumns; ++i ) {
            String sTemp = rs.getString (i);
            currentRow.addElement (sTemp); //rs.getString( i ) );
        }

        return currentRow;
    } /* End method portfolioMainScr::stockPanel::getNextRow */
    /**
    * 0j0 not safe
    */
    public static String getYOrderedAxe (String sStockNameParam, String [] sExcludeColsParam, String sValColParam) throws Exception
    {
        String sIndexSelect = null;
        if (sStockNameParam == null)
            throw new Exception ("stock name not provided.");
        if (sValColParam == null)
            throw new Exception ("train set column not provided.");
        if (sExcludeColsParam == null) {
            throw new Exception ("null sExcludeColsParam parameter");
        }
        if (sExcludeColsParam.length <= 0) {
            throw new Exception ("empty sExcludeColsParam parameter");
        }

        for (int i = 0; i < sExcludeColsParam.length; i++) {
            if (sIndexSelect == null)
                sIndexSelect = sStockNameParam + "." + sExcludeColsParam [i] + ", ";
            else
                sIndexSelect = sIndexSelect + sStockNameParam + "." + sExcludeColsParam [i] + ", ";
        }
        if (sIndexSelect == null)
            sIndexSelect = sStockNameParam + "." + sValColParam + ",";
        else
            sIndexSelect = sIndexSelect + sStockNameParam + "." + sValColParam + ",";

        return sIndexSelect;
    } /* end selectIndexes method */
    /**
     *
     */
    public static String from (String [] sIncludeTblsParam) throws Exception
    {
        String sFrom = null;
        int iTblSize = sIncludeTblsParam.length;
        if (iTblSize < 1) {
            throw new Exception ("IncludeTbls array empty");
        }

        for (int i = 0; i < iTblSize; i++) {
            if (sFrom == null)
                sFrom = sIncludeTblsParam [i] + ", ";
            else
                sFrom = sFrom + sIncludeTblsParam [i] + ", ";
        }
        sFrom = sFrom.substring (0, sFrom.length() -2) + " ";

        return sFrom;
    } /* end method from */
    /**
     *
     */
    public static String whereDates (String sDateIniParam, 
                                     String sDateEndParam, 
                                     String sStockNameParam, 
                                     String sTradeDateParam, 
                                     String [] sIncludeTblsParam)
    {
        String sWhere   = sStockNameParam + "." + sTradeDateParam + " >= " + sDateIniParam       + " AND " +
                          sStockNameParam + "." + sTradeDateParam + " <= " + sDateEndParam       + " AND " ;

        for (int i = 1; i < sIncludeTblsParam.length; i++) {
            sWhere = sWhere +
                     sStockNameParam + "." + sTradeDateParam + " = " + sIncludeTblsParam [i] + "." + sTradeDateParam + " AND ";
        }
        sWhere = sWhere.substring (0, sWhere.length () -5);

        return sWhere;
    } /* end method where */
    /**
     * 
     */
    public static String orderBy (String sStockName, String [] sExcludeCols)
    {
        if (sStockName == null || sExcludeCols == null)
            return null;
        if (sExcludeCols.length <= 0)
            return null;

        return sStockName + "." + sExcludeCols [0];
    }
    /**
     * 
     */
    public static String [] getIncludeTables (String sStockNameParam, String [] sExcludeTbls)
    {   
        int iExcludeTblsLen = sExcludeTbls.length +1;
        String [] sIncludeTbls = new String [iExcludeTblsLen];
        sIncludeTbls [0] = sStockNameParam;
        for (int iIdx = 1; iIdx < iExcludeTblsLen; iIdx ++) {
            sIncludeTbls [iIdx]  = sExcludeTbls [iIdx -1];
        }

        return sIncludeTbls;
    }
//    /**
//     *
//     * @param rs
//     * @param vCurrentRowParam : this argument is necesary to allow this method to work on static context; to use it, just provide an unnamed new Vector ();
//     * @param iColumnsShift
//     * @return
//     * @throws java.sql.SQLException
//     * @throws java.lang.Exception
//     */
//    public static double [][] transferResultSet2Matrix (ResultSet rs, int iColumnsShift) throws SQLException, Exception
//    {
//        if (rs == null) {
//            throw new Exception ("Null resultSet.");
//        }
//
//        rs.last();
//        int iRow = rs.getRow();
//        rs.first();
//
//        if (iRow <= 0 ) {
//            throw new Exception ("ResultSet contained no records" );
//        }
//
//        ResultSetMetaData rsmd = rs.getMetaData ();
//
//        int iColumns = rsmd.getColumnCount();
//
//        if (iRow <= 0 || iColumns <= 0)
//            return null;
//
//        Double DTemp;
//        int iMatrixColLen = iColumns - iColumnsShift;
//        double [][] dMatrix = new double [iRow][iMatrixColLen];
//
//        for (int i = 0; i < iRow; i++) {
//            Vector vCurrentRow = MiscellaneaDB.getCurrentRowFromResultSet (rs, iColumns);
//            int iMtrxColCounter = 0;
//            for (int j = iColumnsShift; j < iColumns; j++) {
//                DTemp = Double.valueOf(vCurrentRow.get(j).toString());
//                dMatrix [i][iMtrxColCounter] =  DTemp.doubleValue ();
// //               System.out.print (dMatrix [i][iMtrxColCounter] + " ");
//                iMtrxColCounter++;
//            }
////            System.out.println ();
//            rs.next ();
////            vCurrentRowParam.removeAllElements();
//        }
//
//        return dMatrix;
//    }
//    public static Object [][] transferObjectResultSet2Matrix (ResultSet rs, int iColumnsShift) throws SQLException, Exception
//    {
//        if (rs == null) {
//            throw new Exception ("Null resultSet.");
//        }
//
//        rs.last();
//        int iRow = rs.getRow();
//        rs.first();
//
//        if (iRow <= 0 ) {
//            throw new Exception ("ResultSet contained no records" );
//        }
//
//        ResultSetMetaData rsmd = rs.getMetaData ();
//
//        int iColumns = rsmd.getColumnCount();
//
//        if (iRow <= 0 || iColumns <= 0)
//            return null;
//
//        Object OTemp;
//        int iMatrixColLen = iColumns - iColumnsShift;
//        Object [][] OMatrix = new Object [iRow][iMatrixColLen];
//
//        for (int i = 0; i < iRow; i++) {
//            Vector vCurrentRow = MiscellaneaDB.getCurrentRowFromResultSet (rs, iColumns);
//            int iMtrxColCounter = 0;
//            for (int j = iColumnsShift; j < iColumns; j++) {
//                OTemp = vCurrentRow.get(j);
//                OMatrix [i][iMtrxColCounter] =  OTemp;
// //               System.out.print (dMatrix [i][iMtrxColCounter] + " ");
//                iMtrxColCounter++;
//            }
////            System.out.println ();
//            rs.next ();
////            vCurrentRowParam.removeAllElements();
//        }
//
//        return OMatrix;
//    }
    /**
     *
     * @param rs 
     * @param iColumns 
     * @return 
     * @throws java.sql.SQLException 
     */
    public static Vector getCurrentRowFromResultSet (ResultSet rs, int iColumns) throws SQLException
    {
        Vector currentRow = new Vector ();

        for ( int i = 1; i <= iColumns; ++i ) {
            String sTemp = rs.getString (i);
            currentRow.addElement (sTemp); //rs.getString( i ) );
        }

        return currentRow;
    } /* End method portfolioMainScr::stockPanel::getNextRow */        
} /* End class PortfolioDB */
